package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.School;
import cn.edu.sdjzu.xg.bysj.service.SchoolService;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j public final class DepartmentDao {
    //DepartmentDao类型的实例
    private static DepartmentDao departmentDao = new DepartmentDao();
    //预编译语句
    private PreparedStatement preparedStatement;
    //结果集
    private ResultSet resultSet;
    //唯一私有无参构造器，避免外部创建对象
    private DepartmentDao() {
    }
    //获得实例
    public static DepartmentDao getInstance() {
        return departmentDao;
    }

    /**
     * 新增
     * @param connection
     * @param department
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection, Department department) throws SQLException {
        //预编译sql语句
        preparedStatement = connection.prepareStatement("insert into department (description,no,remarks,school_id)values (?,?,?,?)");
        //为sql语句参数赋值
        preparedStatement.setString(1,department.getDescription());
        preparedStatement.setString(2,department.getNo());
        preparedStatement.setString(3,department.getRemarks());
        preparedStatement.setInt(4,department.getSchool().getId());
        //执行sql语句并获得受影响的行数得值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,preparedStatement);
        //受影响行数判断
        return rowAffected > 0;
    }

    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delete(Connection connection,Integer id) throws SQLException {
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //预编sql语句
        preparedStatement = connection.prepareStatement("delete from department where id=?");
        //为sql语句参数赋值
        preparedStatement.setInt(1,id);
        //执行sql语句并获得受影响行数得值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭链接
        JdbcHelper.close(null,preparedStatement);
        //受影响行数判断
        return rowAffected > 0;
    }

    /**
     * 更新
     * @param connection
     * @param department
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection, Department department) throws SQLException {
        //预编译sql语句
        preparedStatement = connection.prepareStatement("update department set description=?,no=?,remarks=?,school_id=? where id=?");
        //为sql参数赋值
        preparedStatement.setString(1,department.getDescription());
        preparedStatement.setString(2,department.getNo());
        preparedStatement.setString(3,department.getRemarks());
        preparedStatement.setInt(4,department.getSchool().getId());
        preparedStatement.setInt(5,department.getId());
        //执行sql语句，并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        //受影响行数判断
        return rowAffected>0;
    }
    /**
     * 根据前台请求的分页与条件数据查询
     *
     * @param condition
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Department> findAll(Connection connection,Collection<Condition> condition, Pagination pagination) throws SQLException {
        //departments集合储存查找到的对象数据
        Collection<Department> departments = new TreeSet<Department>();
        int totalNum = CommonDao.getInstance().count(connection,"Department");
        //构建查找sql语句
        StringBuilder select_sql = new StringBuilder("SELECT * FROM department ");
        if (condition != null){
            //如果condition_json带有要求，则构建对应的WHERE子句
            String whereClause = Condition.toWhereClause(JSON.toJSONString(condition));
            //将构建完成的WHERE子句添加到查找sql后
            select_sql.append(whereClause);
            log.trace(select_sql.toString());
        }
        //如果有带有分页请求，构建对应LIMIT子句
        if (pagination != null){
            //将构建完成的LIMIT子句添加到sql后
            String limitClause = pagination.toLimitClause(totalNum);
            select_sql.append(limitClause);
            log.trace(select_sql.toString());
        }
        //预编译sql语句
        preparedStatement = connection.prepareStatement(select_sql.toString());
        //执行sql语句，获得结果集
        resultSet = preparedStatement.executeQuery();
        //查找结果集
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            //获得description值并赋值给foundDepartment
            String description = resultSet.getString("description");
            //获得no值并赋值给foundDepartment
            String no = resultSet.getString("no");
            //获得remarks值并赋值给foundDepartment
            String remarks = resultSet.getString("remarks");
            //获得school_id值查找对应的school对象
            School school = SchoolService.getInstance().find(resultSet.getInt("school_id"));
            Department foundDepartment = new Department(id,description,no,remarks,school);
            //将查找到的对象添加到集合中
            departments.add(foundDepartment);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        //返回超找到的数据集合
        return departments;
    }

    /**
     * 根据id查找
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public Department find(Connection connection,Integer id) throws SQLException {
        //foundDepartment字段 储存查找到的数据
        Department foundDepartment = null;
        //预编译语句
        preparedStatement = connection.prepareStatement("select * from department where id=?");
        //为参数赋值
        preparedStatement.setInt(1,id);
        //执行预编译语句
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //为字段foundDepartment新建对象
            foundDepartment = new Department();
            //将查找到得值赋值给foundDepartment
            foundDepartment.setId(resultSet.getInt("id"));
            foundDepartment.setDescription(resultSet.getString("description"));
            foundDepartment.setNo(resultSet.getString("no"));
            foundDepartment.setRemarks(resultSet.getString("remarks"));
            //获得school_id值查找对应的school对象
            School school = SchoolService.getInstance().find(resultSet.getInt("school_id"));
            foundDepartment.setSchool(school);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的对象
        return foundDepartment;
    }

    /**
     * @param connection
     * @param school
     * @return
     * @throws SQLException
     */
    public int countAll(Connection connection, School school) throws SQLException {
        //预编译sql语句
        this.preparedStatement = connection.prepareStatement("SELECT COUNT(*)as school_id FROM department WHERE school_id=?");
        //为sql语句参数赋值
        this.preparedStatement.setInt(1,school.getId());
        //执行sql语句并获得结果集
        this.resultSet = preparedStatement.executeQuery();
        int deptCount = 0;
        //在结果集获得下属学院的数量
        if (resultSet.next()) {
            deptCount = resultSet.getInt("school_id");
        }
        JdbcHelper.close(resultSet,preparedStatement);
        //返回count值
        return deptCount;
    }
}

